package com.example.douyinserver.collector;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RestController;

import java.sql.*;

//数据库服务
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;


@RestController
public class SQLserver {

    @Autowired(required = false)
    RedisTemplate redisTemplate;

    @Autowired
    JdbcTemplate jdbcTemplate;

    mySQLConnection mc;


    public SQLserver() throws SQLException {
        mc = new mySQLConnection();
    }


    @GetMapping("/douyin/action/{id}")
    public String updateUser(@PathVariable("id") long id) throws SQLException {
        String sql = favoriteAddSQL(id);
        update(sql);
        return "updateOk";
    }


    @GetMapping("/czan/{id}")
    public void createfavoriteList(@PathVariable("id") long videoid) {
        System.out.println(redisTemplate);
        redisTemplate.opsForValue().set(videoid, 0);
        System.out.println(redisTemplate.opsForValue().get(videoid));
    }

    //上传sql语句
    public  void update(String sql) throws SQLException {
        Statement st = mc.conn.createStatement();
        st.execute(sql);
    }
    public ResultSet select(String sql) throws SQLException {
        Statement st = mc.conn.createStatement();
        return st.executeQuery(sql);
    }

    public String login(String username,String password){
        String sql="select id,usertoken from tbl_user where username='"+username+"' AND password='"+password+"';";
        System.out.println(sql);
        return sql;
    }

//    public void update(String sql) throws SQLException {
//        Statement st = mc.conn.createStatement();
//        st.execute(sql);
//    }


    //拼接添加用户SQL语句
    public String addNewUserSQL(long id, String username, String password,
                                String name, String salt, String usertkoen, long follow, long follewed) {

        String sql = "insert into tbl_user  values(" + id + ',' + "'" + username + "'" + ',' + "'" + password + "'" + ',' + "'"
                + name + "'" + ',' + "'" + salt + "'" + ',' + "'" + usertkoen + "'" + ',' + follow + ',' + follewed + ");";
        System.out.println(sql);
        return sql;
    }

    //拼接添加视频SQL语句
    public String addNewVideoSQL(long id, String play_url, String cover_url, int favorite, int comment, String title) {
        String sql = "insert into tbl_video  values(" + id + ',' + "'" + play_url + "'" + ',' + "'" + cover_url + "'" + ','
                + favorite + ',' + comment + ',' + "'" + title + "');";
        System.out.println(sql);
        return sql;
    }

    //拼接添加评论SQL语句
    public String addNewCommentSQL(long id, long vid, String comment, String time) {
        String sql = "insert into tbl_comment  values(" + id + ',' + vid + ','
                + "'" + comment + "'" + ',' + "'" + time + "');";
        System.out.println(sql);
        return sql;
    }

    //拼接关注数加一SQL语句
    public String followAddSQL(long id) {
        String sql = "update tbl_user set follow_count = follow_count+1 where id = " + id + ";";
        System.out.println(sql);
        return sql;
    }

    //拼接粉丝数加一SQL语句
    public String followerAddSQL(long id) {
        String sql = "update tbl_user set follower_count = follower_count+1 where id = " + id + ";";
        System.out.println(sql);
        return sql;
    }

    //拼接点赞数加一SQL语句
    public String favoriteAddSQL(long id) {
        String sql = "update tbl_video set favorite_count = favorite_count+1 where id = " + id + ";";
        System.out.println(sql);
        return sql;
    }

    //拼接评论数加一SQL语句
    public String commentAddSQL(long id) {
        String sql = "update tbl_video set comment_count = comment_count+1 where id = " + id + ";";
        System.out.println(sql);
        return sql;
    }

    //main测试
    public static void main(String args[]) throws SQLException {
        SQLserver s = new SQLserver();
//        String sql[]=new String[7];
//        sql[0]=s.addNewUserSQL(4,"c","bdddbb","c","c","a",10,20);
//        sql[1]=s.addNewVideoSQL(1,"url","url",10,10,"日常");
//        sql[2]=s.addNewCommentSQL(2,1,"赞","2022-05-26 19:43:01");
//        sql[3]=s.commentAddSQL(1);
//        sql[4]=s.favoriteAddSQL(1);
//        sql[5]=s.followAddSQL(1);
//        sql[6]=s.followerAddSQL(1);
//        for(int i=2;i<7;i++)s.update(sql[i]);
//        s.createfavoriteList(1);
    }
}
class mySQLConnection {
    String url = "jdbc:mysql://localhost:3306/douyinserver?serverTimezone=UTC";
    String username = "root";
    String password = "196435";
    Connection conn = null;
    public mySQLConnection() throws SQLException {
        conn = DriverManager.getConnection(url, username, password);
    }
}